package com.gfwy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.gfwy.dao.IChapterDao;
import com.gfwy.vo.Chapter;

public class ChapterDaoImpl implements IChapterDao {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	public ChapterDaoImpl(Connection conn) {
		this.conn = conn;
	}
	@Override
	public boolean doCreate(Chapter chapter) throws Exception {
		boolean flag = false;
		try {
			String sql = "INSERT INTO chapter(cname,pid) VALUES(?,?)";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, chapter.getCname());
			this.pstmt.setInt(2, chapter.getPid());
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	@Override
	public boolean doUpdate(Chapter chapter) throws Exception {
		boolean flag = false;
		try {
			String sql = "UPDATE chapter SET cname=?,pid=? WHERE cid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, chapter.getCname());
			this.pstmt.setInt(2, chapter.getPid());
			this.pstmt.setInt(3, chapter.getCid());
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	@Override
	public boolean doDelete(int cid) throws Exception {
		boolean flag = false;
		try {
			String sql = "DELETE FROM chapter WHERE cid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, cid);
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	@Override
	public List<Chapter> findAll(String keyWord, int startIndex, int count)
			throws Exception {
		List<Chapter> list = null;
		keyWord = (keyWord == null) ? "" : keyWord;
		try {
			String sql = "SELECT period.*,chapter.* FROM chapter LEFT JOIN period ON chapter.pid=period.pid " +
					"WHERE chapter.cname LIKE ? OR period.pname LIKE ? ORDER BY period.pname DESC LIMIT ?,?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, "%" + keyWord + "%");
			this.pstmt.setString(2, "%" + keyWord + "%");
			this.pstmt.setInt(3, startIndex);
			this.pstmt.setInt(4, count);
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<Chapter>();
			while(this.rs.next()) {
				Chapter chapter = new Chapter();
				chapter.setCid(this.rs.getInt("chapter.cid"));
				chapter.setCname(this.rs.getString("chapter.cname"));
				chapter.setPid(this.rs.getInt("chapter.pid"));
				chapter.setPname(this.rs.getString("period.pname"));
				list.add(chapter);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}

	@Override
	public int count(String keyWord) throws Exception {
		int count = 0;
		keyWord = (keyWord == null) ? "" : keyWord;
		try {
			String sql = "SELECT COUNT(*) AS count FROM chapter WHERE cname LIKE ?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, "%" + keyWord + "%");
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()) {
				count = this.rs.getInt("count");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return count;
	}

	@Override
	public Chapter findById(int cid) throws Exception {
		Chapter chapter = null;
		try {
			String sql = "SELECT period.*,chapter.* FROM chapter LEFT JOIN period ON chapter.pid=period.pid WHERE cid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, cid);
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()) {
				chapter = new Chapter();
				chapter.setCid(this.rs.getInt("chapter.cid"));
				chapter.setCname(this.rs.getString("chapter.cname"));
				chapter.setPid(this.rs.getInt("chapter.pid"));
				chapter.setPname(this.rs.getString("period.pname"));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return chapter;
	}

	@Override
	public List<Chapter> findByPid(int pid) throws Exception {
		List<Chapter> list = null;
		try {
			String sql = "SELECT period.*,chapter.* FROM chapter LEFT JOIN period ON chapter.pid=period.pid WHERE chapter.pid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, pid);
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<Chapter>();
			while(this.rs.next()) {
				Chapter chapter = new Chapter();
				chapter.setCid(this.rs.getInt("chapter.cid"));
				chapter.setCname(this.rs.getString("chapter.cname"));
				chapter.setPid(this.rs.getInt("chapter.pid"));
				chapter.setPname(this.rs.getString("period.pname"));
				list.add(chapter);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}

}
